package com.xwdeshj.dal;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.xwdeshj.utils.DateUtil;
import com.xwdeshj.utils.StringUtil;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.rowset.SqlRowSet;

import javax.servlet.http.HttpServletRequest;


/**
 * Created by BKWJA on 2019/1/28.
 * 商家基本信息—SQL类
 * 因jdbcTemplate 加载有相应的顺序，故在该类注解就会报错。
 */
public class Dal_XwBusinessinfo {


    /**
     * 根据商家加密id
     */
    public JSONObject getBusinessinfo(String business_encryptId,JdbcTemplate jdbcTemplate){
        JSONObject result = new JSONObject();
        String sql = "SELECT\n" +
                "  a.business_Legal AS business_Legal,\n" +
                "  b.user_name AS username,\n" +
                "  b.user_mail AS user_mail,\n" +
                "  a.business_createtime AS createtime\n" +
                "FROM xw_business_info a\n" +
                "LEFT JOIN xw_userinfo b ON a.business_encryptId = b.user_encryptId\n";

        StringBuffer sb = new StringBuffer();
        sb.append(" 1=1 ");
        //商家加密id
        if(!business_encryptId.equals("")){
            sb.append(" and a.business_encryptId in('"+business_encryptId+"')");
        }
        try {
            sql = sql + " WHERE "+sb.toString();

            SqlRowSet rs = jdbcTemplate.queryForRowSet(sql);
            while (rs.next()) {
                result.put("business_Legal", (rs.getString("business_Legal") == null ? "" : rs.getString("business_Legal")));
                result.put("username", (rs.getString("username") == null ? "" : rs.getString("username")));
                result.put("user_mail", (rs.getString("user_mail") == null ? "" : rs.getString("user_mail")));
                result.put("createtime", (rs.getString("createtime") == null ? "" : DateUtil.DateToString(rs.getDate("createtime"))));
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        System.out.println("getBusinessinfo:"+result.toString());
        return result;
    }



    /**
     * 获取主管审核列表
     */
    public JSONObject getcheckbusinessinfo(Integer pageindex, Integer pagesize, String starttime,
                                         String endtime, String sortfild, String sortway,String appliacnt,
                                         String business_type,String reviewStatus, String reviewer, String searchval,
                                         String searchkey, JdbcTemplate jdbcTemplate){
        JSONObject result = new JSONObject();
        //获取审核列表
        String sql = "SELECT DISTINCT\n" +
                "  a.business_id,\n" +
                "  a.business_encryptId,\n" +
                "  a.farm_encryptId,\n" +
                "  a.sec_encryptId,\n" +
                "  b.user_realName      AS applicantName,\n" +
                "  b.user_encryptId    AS applicant_encryptId,\n" +
                "  a.business_CompanyName,\n" +
                "  a.business_name,\n" +
                "  a.business_Legal,\n" +
                "  a.business_license,\n" +
                "  a.business_Credit,\n" +
                "  a.business_tel,\n" +
                "  a.business_type,\n" +
                "  a.business_createtime,\n" +
                "  a.business_Remarks,\n" +
                "  a.business_state,\n" +
                "  c.user_encryptId as reviewer_encryptId,\n" +
                "  c.user_realName as reviewerName\n" +
                "FROM xw_business_info a LEFT JOIN xw_userinfo b ON a.applicant_checkencryptId =b.user_encryptId\n" +
                "  LEFT JOIN xw_userinfo c on a.reviewer_checkencryptId = c.user_encryptId \n";

        StringBuffer sb = new StringBuffer();
        sb.append(" 1=1 ");
        //时间查询
        if (!starttime.equals("")) {
            starttime += " 00.00.01";
            endtime += " 23.59.59";
            sb.append(" and a.business_createtime >= '" + starttime + "' and a.business_createtime <= '" + endtime + "' ");
        }
        //搜索内容查询
        if (!searchval.equals("")) {
            sb.append(" and ( a." + searchkey + " like '%" + searchval + "%' )");
        }
        //申请人下拉查询
        if (!appliacnt.equals("")) {
            sb.append(" and ( a.applicant_checkencryptId in('" + appliacnt + "') )");
        }
        //商户类型查询
        if (!business_type.equals("")) {
            sb.append(" and ( a.business_type in('" + business_type + "') )");
        }
        //审核状态查询
        if (!reviewStatus.equals("")) {
            sb.append(" and ( a.business_state in('" + reviewStatus + "') )");
        }
        //已拒绝，已审核只显示3个月内数据
        if (!reviewStatus.equals("") && !reviewStatus.equals("0")) {
            String  date7 = StringUtil.getdate1(-90) + " 00:00:01";
            sb.append(" and (a.business_updatetime >= '" + date7 +"' )");
        }
        //审核人下拉查询
        if (!reviewer.equals("")) {
            sb.append(" and ( a.reviewer_checkencryptId in('" + reviewer + "') )");
        }
        //排序
        if (!sortfild.equals("")) {
            sb.append(" order by a." + sortfild + " " + sortway + " ");
        }
        try {
            sql = sql + " where " + sb.toString() + " limit " + (pageindex - 1) * pagesize + "," + pagesize;
            //分页SQL
            String sql2 = "select count(*) " +
                    "FROM xw_business_info a " +
                    "LEFT JOIN xw_userinfo b ON a.applicant_checkencryptId =b.user_encryptId\n " +
                    "LEFT JOIN xw_userinfo c on a.reviewer_checkencryptId = c.user_encryptId where " + sb.toString();

            // 获取审核人列表
            String sql3 = "SELECT DISTINCT\n" +
                    "  b.user_realName      AS reviewerName,\n" +
                    "  b.user_encryptId AS reviewer_encryptId\n" +
                    "FROM xw_business_info a " +
                    "LEFT JOIN xw_userinfo b ON a.reviewer_checkencryptId = b.user_encryptId\n" +
                    "WHERE a.reviewer_checkencryptId IS NOT NULL ";

            //获取申请人列表
            String sql4 = "SELECT DISTINCT\n" +
                    "  b.user_realName      AS appliantName,\n" +
                    "  b.user_encryptId AS applicant_encryptId\n" +
                    "FROM xw_business_info a LEFT JOIN xw_userinfo b ON a.applicant_checkencryptId = b.user_encryptId\n" +
                    "WHERE a.applicant_checkencryptId IS NOT NULL ";

            SqlRowSet rs = jdbcTemplate.queryForRowSet(sql);
            SqlRowSet rs2 = jdbcTemplate.queryForRowSet(sql2);
            SqlRowSet rs3 = jdbcTemplate.queryForRowSet(sql3);
            SqlRowSet rs4 = jdbcTemplate.queryForRowSet(sql4);

            result.put("status", 0);
            result.put("msg", "ok");
            while (rs2.next()) {
                Integer count = rs2.getInt(1);
                result.put("recordcount", count);
                if (count % pagesize == 0) {
                    result.put("pagecount", count / pagesize);
                } else {
                    result.put("pagecount", count / pagesize + 1);
                }
            }

            //审核人列表
            JSONArray reviewerArray = new JSONArray();
            while (rs3.next()){
                JSONObject object = new JSONObject();
                object.put("reviewerName",rs3.getString("reviewerName"));
                object.put("reviewer_encryptId",rs3.getString("reviewer_encryptId"));
                reviewerArray.add(object);
            }
            result.put("reviewlist",reviewerArray);

            //申请人列表
            JSONArray appliacntArray = new JSONArray();
            while (rs4.next()){
                JSONObject object = new JSONObject();
                object.put("appliantName",rs4.getString("appliantName"));
                object.put("applicant_encryptId",rs4.getString("applicant_encryptId"));
                appliacntArray.add(object);
            }
            result.put("applylist",appliacntArray);

            //数据列表
            JSONArray jsonArray = new JSONArray();
            while (rs.next()) {
                JSONObject object = new JSONObject();
                object.put("business_id", rs.getString("business_id"));
                object.put("business_encryptId", rs.getString("business_encryptId"));
                object.put("farm_encryptId", rs.getString("farm_encryptId") == null ? "" : rs.getString("farm_encryptId"));
                object.put("sec_encryptId", rs.getString("sec_encryptId") == null ? "" : rs.getString("sec_encryptId"));

                object.put("applicantName", rs.getString("applicantName") == null ? "" : rs.getString("applicantName"));
                object.put("applicant_encryptId", rs.getString("applicant_encryptId") == null ? "" : rs.getString("applicant_encryptId"));

                object.put("business_name", rs.getString("business_name") == null ? "" : rs.getString("business_name"));
                object.put("CompanyName", rs.getString("business_CompanyName") == null ? "" : rs.getString("business_CompanyName"));
                object.put("business_Legal", rs.getString("business_Legal") == null ? "" : rs.getString("business_Legal"));
                object.put("business_license", rs.getString("business_license") == null ? "" : rs.getString("business_license"));
                object.put("business_Credit", rs.getString("business_Credit") == null ? "" : rs.getString("business_Credit"));
                object.put("business_tel", rs.getString("business_tel") == null ? "" : rs.getString("business_tel"));
                object.put("business_type", rs.getString("business_type") == null ? "" : rs.getString("business_type"));
                object.put("business_createtime", DateUtil.DateToString(rs.getDate("business_createtime")));
                object.put("remarks", rs.getString("business_Remarks") == null ? "" : rs.getString("business_Remarks"));
                object.put("business_state", rs.getString("business_state") == null ? "" : rs.getString("business_state"));

                object.put("reviewer_encryptId", rs.getString("reviewer_encryptId") == null ? "" : rs.getString("reviewer_encryptId"));
                object.put("reviewerName", rs.getString("reviewerName") == null ? "" : rs.getString("reviewerName"));

                jsonArray.add(object);
            }
            result.put("list", jsonArray);
        } catch (Exception e) {
            e.printStackTrace();
            result.put("status", 500);
            result.put("msg", e);
        }
        System.out.println("getcheckbusinessinfo:"+result.toString());
        return result;
    }


    //获取商家信息列表
    public JSONObject getbusinessinfolist(Integer pageindex, Integer pagesize, String starttime, String endtime,
                                          String sortfild, String sortway, String business_type, String reviewStatus,
                                          String searchval, String searchkey,JdbcTemplate jdbcTemplate) {
        JSONObject result = new JSONObject();

        String sql = "SELECT  xw_business.business_id, xw_business.business_encryptId,xw_business.farm_encryptId, xw_business.sec_encryptId,xw_business.business_tel, xw_business.business_name,xw_business.business_Legal,xw_business.business_license,xw_business.business_Credit,xw_business.business_CompanyName,xw_business.business_type, xw_business.business_create,xw_business.business_state,xw_business.business_Remarks from xw_business_info xw_business ";

        StringBuffer sb = new StringBuffer();
        sb.append(" 1=1 ");
        //时间查询
        if (!starttime.equals("")) {
            starttime += " 00.00.01";
            endtime += " 23.59.59";
            sb.append(" and business_createtime >= '" + starttime + "' and business_createtime <= '" + endtime + "' ");
        }
        //搜索内容查询
        if (!searchval.equals("")) {
            sb.append(" and ( a." + searchkey + " like '%" + searchval + "%' )");
        }
        //商户类型查询
        if (!business_type.equals("")) {
            sb.append(" and ( business_type in(" + business_type + ") )");
        }
        //审核状态查询
        if (!reviewStatus.equals("")) {
            sb.append(" and ( business_state in(" + reviewStatus + ") )");
        }
        //排序
        if (!sortfild.equals("")) {
            sb.append(" order by " + sortfild + " " + sortway + " ");
        }
        try {
            sql = sql + " where " + sb.toString() + " limit " + (pageindex - 1) * pagesize + "," + pagesize;
            //分页SQL
            String sql2 = "select count(*) from xw_business_info where " + sb.toString();
            SqlRowSet rs = jdbcTemplate.queryForRowSet(sql);
            SqlRowSet rs2 = jdbcTemplate.queryForRowSet(sql2);
            result.put("status", 0);
            result.put("msg", "ok");
            while (rs2.next()) {
                Integer count = rs2.getInt(1);
                result.put("recordcount", count);
                if (count % pagesize == 0) {
                    result.put("pagecount", count / pagesize);
                } else {
                    result.put("pagecount", count / pagesize + 1);
                }
            }
            JSONArray jsonArray = new JSONArray();
            while (rs.next()) {
                JSONObject object = new JSONObject();
                object.put("business_id", rs.getString("business_id"));
                object.put("business_encryptId", rs.getString("business_encryptId"));
                object.put("sec_encryptId", rs.getString("sec_encryptId") == null ? "" : rs.getString("sec_encryptId"));
                object.put("farm_encryptId", rs.getString("farm_encryptId") == null ? "" : rs.getString("farm_encryptId"));
                object.put("business_tel", rs.getString("business_tel") == null ? "" : rs.getString("business_tel"));
                object.put("business_name", rs.getString("business_name") == null ? "" : rs.getString("business_name"));
                object.put("business_Legal", rs.getString("business_Legal") == null ? "" : rs.getString("business_Legal"));
                object.put("business_license", rs.getString("business_license") == null ? "" : rs.getString("business_license"));
                object.put("business_Credit", rs.getString("business_Credit") == null ? "" : rs.getString("business_Credit"));
                object.put("CompanyName", rs.getString("business_CompanyName") == null ? "" : rs.getString("business_CompanyName"));
                object.put("business_type", rs.getString("business_type") == null ? "" : rs.getString("business_type"));
                object.put("business_create", rs.getString("business_create"));
                object.put("business_state", rs.getString("business_state") == null ? "" : rs.getString("business_state"));
                object.put("remarks", rs.getString("business_Remarks") == null ? "" : rs.getString("business_Remarks"));
                jsonArray.add(object);
            }
            result.put("list", jsonArray);
        } catch (Exception e) {
            e.printStackTrace();
            result.put("status", 500);
            result.put("msg", e);
        }
        System.out.println("getbusinessinfolist:" + result.toString());
        return result;
    }

}
